<h1>MOD函数索引实战:解决百万级数据分批处理性能瓶颈</h1>
问题背景
GreatSQL的MOD函数,大家应该都不陌生,使用MOD函数创建函数索引,是不是很少有人这么用呀,下面听我讲讲使用MOD函数创建函数索引的故事吧。
故事的引子呢,是有这么一个使用场景,为了忽略客户真实的业务,对涉及的表只保留了别名。
SELECT g.*
FROM g
JOIN a ON g.customer_id = a.customer_id
JOIN d ON a.customer_code = d.customer_code
JOIN f ON g.subs_id = f.subs_id
JOIN c ON a.company_id = c.province_id
JOIN e ON d.city = e.city_id
WHERE g.bill_month = 202505
AND g.deal_status = 1
AND (MOD(g.bill_seq, 6) + 1) = 1
LIMIT 2000
这个场景是对业务数据分批处理,每次取2000条数据,使用MOD函数分6个通道并行处理,每批处理完后会修改deal_status
的状态,每月涉及的数据量约两百万,客户描述这个SQL越执行越慢,后面执行会达到20s。
通过执行计划分析获知,SQL主要慢在了对g表按照 WHERE 条件读取数据上,这个g表在 deal_status
上有单列索引。由于业务处理的复杂性,并不总是6个通道齐头并进的处理的,如果符合deal_status的条件多,但是满足MOD函数条件的数据少,g表读取满足条件的2000条数据就会耗时久。考虑到这种情况,决定在deal_status,mod(g.bill_seq, 6) + 1
上建联合函数索引。函数索引生效后,SQL执行效率显著提升,性能稳定在毫秒级别,不因数据变化而变化。
为什么此处建联合函数索引能提升效率呢?
MOD(g.bill_seq, 6)的取值只有6个值,函数索引的选择性并不好,之所以建联合函数索引能提升效率,是因为数据处理过程中数据会变得不均匀,而该业务SQL有LIMIT 关键字限制,只提取前2000行数据,故通过联合函数索引精确定位后,可快速取得数据。
下面介绍一下MOD函数索引创建过程中遇到的插曲故事。
实验验证
准备工作
建表t1,通过存储过程p1填充了10000行数据。
CREATE TABLE t1(
bill_seq DECIMAL(20,0),
bill_month INT,
deal_status DECIMAL(2,0)
)
PARTITION BY RANGE (bill_month)
(PARTITION p1 VALUES LESS THAN (202506));
DELIMITER //
CREATE OR REPLACE PROCEDURE p1 IS
BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO t1(bill_seq,bill_month,deal_status) VALUES(i,202505,1);
END LOOP;
UPDATE t1 SET deal_status=2 WHERE bill_seq<9000 AND MOD(bill_seq,6)+1=6;
END;
//
DELIMITER ;
CALL p1();
查询数据
greatsql> SELECT COUNT(*) FROM t1 WHERE deal_status=1;
+----------+
| count(*) |
+----------+
| 8500 |
+----------+
1 row in set (0.00 sec)
greatsql> SELECT COUNT(*) FROM t1 WHERE deal_status=1 AND MOD(bill_seq,6)+1=6;
+----------+
| count(*) |
+----------+
| 166 |
+----------+
1 row in set (0.02 sec)
greatsql> SELECT COUNT(*) FROM t1 WHERE deal_status=1 AND MOD(bill_seq,6)+1=1;
+----------+
| count(*) |
+----------+
| 1666 |
+----------+
1 row in set (0.03 sec)
业务场景验证
根据造数规则,可以看出满足deal_status=1的数据量多,deal_status=1 and mod(bill_seq,6)+1=6 的数据量不多。
表t1 上增加deal_status的单列索引,查询两个语句的执行计划。
ALTER TABLE t1 ADD INDEX idx_deal_status(deal_status);
SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=6 LIMIT 10;
SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=1 LIMIT 10;
执行计划如下
greatsql> EXPLAIN ANALYZE SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=6 LIMIT 10;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s) (cost=106.00 rows=10) (actual time=21.632..21.819 rows=10 loops=1)
-> Filter: ((t1.bill_month = 202505) and (((t1.bill_seq % 6) + 1) = 6)) (cost=106.00 rows=850) (actual time=21.631..21.816 rows=10 loops=1)
-> Index lookup on t1 using idx_deal_status (deal_status=1) (cost=106.00 rows=8500) (actual time=0.027..18.503 rows=7559 loops=1)
1 row in set (0.02 sec)
greatsql> EXPLAIN ANALYZE SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=1 LIMIT 10;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s) (cost=106.00 rows=10) (actual time=0.050..0.170 rows=10 loops=1)
-> Filter: ((t1.bill_month = 202505) and (((t1.bill_seq % 6) + 1) = 1)) (cost=106.00 rows=850) (actual time=0.049..0.168 rows=10 loops=1)
-> Index lookup on t1 using idx_deal_status (deal_status=1) (cost=106.00 rows=8500) (actual time=0.029..0.142 rows=50 loops=1)
1 row in set (0.00 sec)
查询deal_status=1 and mod(bill_seq,6)+1=6
的SQL,需要扫描7559行数据来获取10条数据。
查询deal_status=1 and mod(bill_seq,6)+1=1
的SQL, 只需扫描50行就能获取10条数据。
可以说明,真实业务案例中,分6个通道读取数据时会遇到这种情况,数据变化造成SQL执行效率不稳定。
创建函数索引
下面建联合函数索引,查询SQL执行计划。
ALTER TABLE t1 DROP INDEX idx_deal_status;
ALTER TABLE t1 ADD INDEX idx_dealstaus_billseq(deal_status,(MOD(bill_seq,6)+1));
greatsql>EXPLAIN SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=6 LIMIT 10;
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | p1 | ref | idx_dealstaus_billseq | idx_dealstaus_billseq | 2 | const | 5000 | 10.00 | Using where |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
greatsql>EXPLAIN ANALYZE SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=6 LIMIT 10;
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s) (cost=71.00 rows=10) (actual time=27.500..27.530 rows=10 loops=1)
-> Filter: ((t1.bill_month = 202505) and (((t1.bill_seq % 6) + 1) = 6)) (cost=71.00 rows=500) (actual time=27.499..27.528 rows=10 loops=1)
-> Index lookup on t1 using idx_dealstaus_billseq (deal_status=1) (cost=71.00 rows=5000) (actual time=0.036..23.827 rows=8344 loops=1)
1 row in set (0.02 sec)
从执行计划可以看出,只用到了deal_status
一列的索引来定位,(mod(bill_seq,6)+1)
并没有起到定位的作用,需要扫描8344行数据来获取10条数据。
函数索引失效了,为什么?
索引失效问题解决
为了解决问题,采取了曲线救国政策,增加虚拟列,通过虚拟列建联合索引。
ALTER TABLE t1 ADD c1 INT GENERATED ALWAYS AS (MOD(bill_seq,6)+1);
ALTER TABLE t1 ADD INDEX idx_deal_c1(deal_status,c1);
greatsql> EXPLAIN SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=6 LIMIT 10;
+----+-------------+-------+------------+------+-----------------------------------+-------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------------------+-------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | t1 | p1 | ref | idx_dealstaus_billseq,idx_deal_c1 | idx_deal_c1 | 7 | const,const | 166 | 10.00 | Using where |
+----+-------------+-------+------------+------+-----------------------------------+-------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
greatsql> EXPLAIN ANALYZE SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=6 LIMIT 10;\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s) (cost=22.66 rows=10) (actual time=0.098..0.171 rows=10 loops=1)
-> Filter: (t1.bill_month = 202505) (cost=22.66 rows=17) (actual time=0.088..0.160 rows=10 loops=1)
-> Index lookup on t1 using idx_deal_c1 (deal_status=1, c1=6) (cost=22.66 rows=166) (actual time=0.087..0.156 rows=10 loops=1)
1 row in set (0.00 sec)
从执行计划可以看出,用上了虚拟列的联合索引,只需扫描10行数据就能获取到10行数据,两个条件都起到了定位作用。
问题是解决了,可是回过头来想一想,虚拟列与函数索引有何不同,理论上是一样的,没道理函数索引不行。
不知道细心的你有没有发现一个问题,建虚拟列时我指定了列类型为INT,而创建表时bill_seq列为DECIMAL类型。所以问题聚焦在MOD函数究竟返回了什么类型,(MOD(bill_seq,6)+1) =6
是否发生了隐式转换,导致索引不可用。
GreatSQL对MOD函数的参数管理比较宽松,MOD(N,M)
, 两个参数一般是整形数值,也可以是定点和浮点类型数值,返回值可以是小数。 本案例中MOD(bill_seq,6)
,bill_seq为DECIMAL(20,0)
类型, 返回值应与参数bill_seq类型一致。
一般情况下发生隐式转换,都是认为列类型为VARCHAR,而条件值给了数值,但是MOD函数是数值类型函数,返回数值类型的数据,所以开始一直没有往隐式转换这方面想。
在DECIMAL与INT之间难道也存在隐式转换?根据以往经验来看,列类型为decimal类型时,直接传值数值,是没有问题的,本例中deal_status
为DECIMAL(2,0)
类型,是能用上索引的。
本着试一试的态度,将条件值使用CAST函数转换成DECIMAL类型。
greatsql> EXPLAIN SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=CAST(6 AS DECIMAL) LIMIT 10;
+----+-------------+-------+------------+------+-----------------------------------+-----------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------------------+-----------------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | t1 | p1 | ref | idx_dealstaus_billseq,idx_deal_c1 | idx_dealstaus_billseq | 13 | const,const | 166 | 10.00 | Using where |
+----+-------------+-------+------------+------+-----------------------------------+-----------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
greatsql> EXPLAIN ANALYZE SELECT * FROM t1 WHERE bill_month=202505 AND deal_status=1 AND MOD(bill_seq,6)+1=CAST(6 AS DECIMAL) LIMIT 10\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s) (cost=22.66 rows=10) (actual time=0.083..0.126 rows=10 loops=1)
-> Filter: (t1.bill_month = 202505) (cost=22.66 rows=17) (actual time=0.077..0.118 rows=10 loops=1)
-> Index lookup on t1 using idx_dealstaus_billseq (deal_status=1, ((bill_seq % 6) + 1)=6) (cost=22.66 rows=166) (actual time=0.075..0.115 rows=10 loops=1)
1 row in set (0.00 sec)
从执行计划可以看出,使用了开始创建的函数索引 idx_dealstaus_billseq(deal_status,(mod(bill_seq,6)+1))
。MOD函数部分也起到了定位作用,是否有处处有惊喜的感觉。
总结:
1.MOD函数返回值类型取决于参数值类型,参数既有DECIMAL,又有INT的情况下,会返回DECIMAL类型。
2.MOD函数索引使用了DECIMAL类型的参数时,条件值也要使用DECIMAL类型,才能用上该函数索引定位。
3.虚拟列默认不占用存储空间,但可以指定列的类型,会间接影响其生成表达式的类型。
Enjoy GreatSQL :)
关于 GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库,具备高性能、高可靠、高易用性、高安全等多个核心特性,可以作为MySQL或Percona Server的可选替换,用于线上生产环境,且完全免费并兼容MySQL或Percona Server。
相关链接: GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区:
社区有奖建议反馈: https://greatsql.cn/thread-54-1-1.html
社区博客有奖征稿详情: https://greatsql.cn/thread-100-1-1.html
(对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~)
技术交流群:
微信&QQ群:
QQ群:533341697
微信群:添加GreatSQL社区助手(微信号:wanlidbc
)好友,待社区助手拉您进群。
</div>
维权提醒:如果你或身边的朋友近五年内因投顾公司虚假宣传、诱导交费导致亏损,别放弃!立即联系小羊维权(158 2783 9931,微信同号),专业团队帮你讨回公道! 📞立即免费咨询退费